﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;
using Westwind.Web;
using Westwind.Web.Controls;
using Westwind.Utilities;
using Westwind.Web.JsonSerializers;

namespace WebQueryRole
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            BPAuthentication.IsLoggedIn(this, _dbCollection);

            // reset anything that should be blank on a new run
            divErrors.InnerHtml = "";

            // load up everything we should see everytime we load the page
            // javascript vars, listbox values, etc
            try
            {
                LoadPageVars();
            }
            catch (Exception ex)
            {
                ReportErrorToHTML(ex.Message);
            }

            // if are loading this page due to a submit button, determine which one was pressed            
            if (!String.IsNullOrEmpty(sqlTextBox.Value))
            {
                int maxRecords = 0; // unlimited
                int.TryParse(limitSelect.SelectedValue,out maxRecords);
                HandleSQLQuery(sqlTextBox.Value, maxRecords);
            }
            
        }

        /// <summary>
        /// this function manages how to add errors (if multiple) to the error report div inside the Default.aspx page
        /// </summary>
        /// <param name="msg"></param>
        private void ReportErrorToHTML(string msg)
        {
            divErrors.InnerHtml += "<p style=\"color:red;\">" + msg.Replace("\n", "<br />") + "</p>";    
        }

        /// <summary>
        /// database object, this shouhld always be intialized, but dont know where to initalize at
        /// </summary>
        private DBCollection _dbCollection = new DBCollection();

        protected override void OnLoadComplete(EventArgs e)
        {
            base.OnLoadComplete(e);            
            _dbCollection.CloseAll();            
        }               

        private void LoadPageVars()
        {
            List<JsonSqlVar> vars = JsonSqlVar.LoadStore(_dbCollection["web"]);
            LoadJavascriptVars(vars);

            querySelectList.Items.Clear();
            foreach (JsonSqlVar sqlVar in vars)
            {
                string shortDescTrimmed = sqlVar.short_desc;
                if (shortDescTrimmed.Length > 15)
                {
                    //shortDescTrimmed = shortDescTrimmed.Substring(0, 13) + "..";
                }
                // since the short desc name is unique, we can use it as a value
                querySelectList.Items.Add(new ListItem(shortDescTrimmed, sqlVar.short_desc));
            }

            if (!String.IsNullOrEmpty(Request["sql_code"]))
            {
                sqlTextBox.Value = Request["sql_code"];
            }
        }

        private void SkinDataGrid(DataGrid dg)
        {
            dg.CssClass = "sql_result";
            dg.AlternatingItemStyle.CssClass = "row_odd";
            dg.ItemStyle.CssClass = "row_even";
            dg.HeaderStyle.CssClass = "row_header";
        }

        private void HandleSQLQuery(string query, int maxRecords)
        {
            SqlConnection conn = _dbCollection["data"];

            // for now assume its always a select query
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.CommandTimeout = 300;
            int recordsFound = 0;
            
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                if(maxRecords > 0) {
                    recordsFound = adapter.Fill(ds, 0, maxRecords, "source");
                }
                else {
                    recordsFound = adapter.Fill(ds);
                }
                
                DataGrid dg = new DataGrid();
                dg.DataSource = ds;
                dg.DataBind();
                SkinDataGrid(dg);
                myPlaceHolder.Controls.Add(dg);

                divSqlRowsFound.InnerHtml = "Your query generated " + recordsFound + " result(s)";
            }
            catch (Exception e)
            {
                ReportErrorToHTML(e.Message);
            }
        }

        // javascript variables loaded on page load
        public string jsonSQLStore = "null";

        private void LoadJavascriptVars(List<JsonSqlVar> vars)
        {
            JSONSerializer ser = new JSONSerializer();
            jsonSQLStore = ser.Serialize(vars);
        }       

        

        // ajax calls
        [CallbackMethod]
        public string ajaxSaveSqlVar(string shortDesc, string longDesc, string sqlCode)
        {
            JsonSqlVar var = JsonSqlVar.SaveSqlVar(shortDesc, longDesc, sqlCode, _dbCollection["web"]);
            JSONSerializer ser = new JSONSerializer();
            return ser.Serialize(var);
        }

        [CallbackMethod]
        public string ajaxDeleteSqlVar(string shortDesc)
        {
            try
            {
                JsonSqlVar.DeleteSqlVar(shortDesc, _dbCollection["web"]);
                return "true";
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception (ajaxDeleteSqlVar): " + e.Message);
                return "false";
            }
        }
    }
}
